package org.anderground.app.storage.db;

import org.anderground.app.storage.ConfigReader;
import org.anderground.app.storage.ConfigWriter;
import org.anderground.app.storage.OnProgressCallback;
import org.anderground.app.storage.PersistentStorage;
import org.anderground.app.storage.ConfigData.ConfigCity;
import org.anderground.core.Logger;
import org.anderground.core.storage.ModelReader;
import org.anderground.core.storage.ModelWriter;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author Eldar Abusalimov
 */
public class DbStorage extends PersistentStorage {

	private final Context context;
	private final DbHelper dbHelper;

	private static DbStorage instance = null;

	/**
	 * @param context
	 * @return the instance
	 */
	public static DbStorage getInstance(Context context) {
		if (instance == null) {
			instance = new DbStorage(context.getApplicationContext());
		}
		if (instance.context != context.getApplicationContext()) {
			Logger.w("instance.appContext != context.getApplicationContext()");
		}
		return instance;
	}

	/**
	 * @param context
	 *            the context of the single, global Application object of the
	 *            current process
	 */
	private DbStorage(Context context) {
		this.context = context;
		dbHelper = new DbHelper(context);
//		// FIXME
//		dbHelper.onUpgrade(getWritableDatabase(), 1, 2);
	}

	/** {@inheritDoc} */
	@Override
	protected ModelReader getReaderForCity(ConfigCity city) {
		return new DbModelReader(dbHelper, city.getId());
	}

	/** {@inheritDoc} */
	@Override
	protected ModelWriter getWriterForCity(ConfigCity city) {
		return new DbModelWriter(dbHelper, city.getId());
	}

	/** {@inheritDoc} */
	@Override
	protected ConfigReader getConfigReader(OnProgressCallback onProgressCallback) {
		return new DbConfigReader(dbHelper, onProgressCallback);
	}

	/** {@inheritDoc} */
	@Override
	protected ConfigWriter getConfigWriter(OnProgressCallback onProgressCallback) {
		return new DbConfigWriter(dbHelper, onProgressCallback);
	}

	/**
	 * @return a database object valid until {@link #getWritableDatabase()} or
	 *         {@link #close()} is called.
	 * @see android.database.sqlite.SQLiteOpenHelper#getReadableDatabase()
	 */
	public SQLiteDatabase getReadableDatabase() {
		return dbHelper.getReadableDatabase();
	}

	/**
	 * @return a read/write database object valid until {@link #close()} is
	 *         called
	 * @see android.database.sqlite.SQLiteOpenHelper#getWritableDatabase()
	 */
	public SQLiteDatabase getWritableDatabase() {
		return dbHelper.getWritableDatabase();
	}

	/**
	 * Close any open database object.
	 * 
	 * @see android.database.sqlite.SQLiteOpenHelper#close()
	 */
	public void close() {
		dbHelper.close();
	}

	@Override
	protected void finalize() throws Throwable {
		close();
		super.finalize();
	}

	private static String indexName(String table, String field) {
		return "i_" + table + "_" + field;
	}

	/**
	 * @param db
	 */
	private static void createTables(SQLiteDatabase db) {
		// "t" prefix means table or table column
		// "v" prefix means view or view column
		// "i" prefix means index
		//
		// presence of "_" shows that the variable presents a column
		//
		// "_Full" suffix on a column means that the column name is prefixed by
		// its table name

		Logger.i("DbStorage: creating tables");

		final String tMaps = MapsTable.NAME;
		final String tMaps_Id = MapsTable.Columns._ID;
		final String tMaps_LastModified = MapsTable.Columns.LAST_MODIFIED;

		final String tCountries = CountriesTable.NAME;
		final String tCountries_Id = CountriesTable.Columns._ID;
		final String tCountries_Code = CountriesTable.Columns.CODE;
		final String tCountries_Name = CountriesTable.Columns.NAME;

		final String tCities = CitiesTable.NAME;
		final String tCities_Id = CitiesTable.Columns._ID;
		final String tCities_MapId = CitiesTable.Columns.MAP_ID;
		final String tCities_CountryId = CitiesTable.Columns.COUNTRY_ID;
		final String tCities_Name = CitiesTable.Columns.NAME;
		final String tCities_Url = CitiesTable.Columns.URL;
		final String tCities_Latitude = CitiesTable.Columns.LATITUDE;
		final String tCities_Longitude = CitiesTable.Columns.LONGITUDE;

		final String tLines = LinesTable.NAME;
		final String tLines_Id = LinesTable.Columns._ID;
		final String tLines_MapId = LinesTable.Columns.MAP_ID;
		final String tLines_Color = LinesTable.Columns.COLOR;
		final String tLines_Name = LinesTable.Columns.NAME;
		final String tLines_TrainDelay = LinesTable.Columns.TRAIN_DELAY;

		final String tStations = StationsTable.NAME;
		final String tStations_Id = StationsTable.Columns._ID;
		final String tStations_MapId = StationsTable.Columns.MAP_ID;
		final String tStations_Name = StationsTable.Columns.NAME;
		final String tStations_Latitude = StationsTable.Columns.LATITUDE;
		final String tStations_Longitude = StationsTable.Columns.LONGITUDE;
		final String tStations_Open = StationsTable.Columns.OPEN;
		final String tStations_Close = StationsTable.Columns.CLOSE;
		final String tStations_PositionX = StationsTable.Columns.POSITION_X;
		final String tStations_PositionY = StationsTable.Columns.POSITION_Y;
		final String tStations_LabelDx = StationsTable.Columns.LABEL_DX;
		final String tStations_LabelDy = StationsTable.Columns.LABEL_DY;
		final String tStations_LabelAngle = StationsTable.Columns.LABEL_ANGLE;
		final String tStations_LabelVisible = StationsTable.Columns.LABEL_VISIBLE;

		final String tTunnels = TunnelsTable.NAME;
		final String tTunnels_Id = TunnelsTable.Columns._ID;
		final String tTunnels_Time = TunnelsTable.Columns.TIME;
		final String tTunnels_LineId = TunnelsTable.Columns.LINE_ID;
		final String tTunnels_Smooth = TunnelsTable.Columns.SMOOTH;
		final String tTunnels_ToStationId = TunnelsTable.Columns.TO_STATION_ID;
		final String tTunnels_FromStationId = TunnelsTable.Columns.FROM_STATION_ID;

		final String tTransfers = TransfersTable.NAME;
		final String tTransfers_Id = TransfersTable.Columns._ID;
		final String tTransfers_Time = TransfersTable.Columns.TIME;
		final String tTransfers_ToStationId = TransfersTable.Columns.TO_STATION_ID;
		final String tTransfers_FromStationId = TransfersTable.Columns.FROM_STATION_ID;

		final String tCityAliases = CityAliasesTable.NAME;
		final String tCityAliases_Id = CityAliasesTable.Columns._ID;
		final String tCityAliases_Name = CityAliasesTable.Columns.NAME;
		final String tCityAliases_CityId = CityAliasesTable.Columns.CITY_ID;

		final String tStationAliases = StationAliasesTable.NAME;
		final String tStationAliases_Id = StationAliasesTable.Columns._ID;
		final String tStationAliases_Name = StationAliasesTable.Columns.NAME;
		final String tStationAliases_StationId = StationAliasesTable.Columns.STATION_ID;

		final String iCities_MapId = indexName(tCities, tCities_MapId);
		final String iCities_Name = indexName(tCities, tCities_Name);
		final String iCities_Latitude = indexName(tCities, tCities_Latitude);
		final String iCities_Longitude = indexName(tCities, tCities_Longitude);

		final String iLines_MapId = indexName(tLines, tLines_MapId);

		final String iStations_Name = indexName(tStations, tStations_Name);
		final String iStations_MapId = indexName(tStations, tStations_MapId);

		final String iTunnels_FromStationId = indexName(tTunnels,
				tTunnels_FromStationId);
		final String iTransfers_FromStationId = indexName(tTransfers,
				tTransfers_FromStationId);

		final String iCityAliases_Name = indexName(tCityAliases,
				tCityAliases_Name);
		final String iCityAliases_CityId = indexName(tCityAliases,
				tCityAliases_CityId);
		
		final String iStationAliases_Name = indexName(tStationAliases,
				tStationAliases_Name);
		final String iStationAliases_StationId = indexName(tStationAliases,
				tStationAliases_StationId);

		db.execSQL("CREATE TABLE " + tMaps + " ( "
				+ tMaps_Id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ tMaps_LastModified + " INTEGER"
		+ " );");
		db.execSQL("CREATE TABLE " + tCountries + " ( "
				+ tCountries_Id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ tCountries_Code + " TEXT UNIQUE,"
				+ tCountries_Name + " TEXT"
		+ " );");
		db.execSQL("CREATE TABLE " + tCities + " ( "
				+ tCities_Id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ tCities_MapId + " INTEGER UNIQUE REFERENCES "
					+ tMaps + "(" + tMaps_Id + ") ON DELETE SET NULL,"
				+ tCities_CountryId + " INTEGER REFERENCES "
					+ tCountries + "(" + tCountries_Id + ") ON DELETE CASCADE,"
				+ tCities_Name + " TEXT NOT NULL,"
				+ tCities_Url + " TEXT NOT NULL,"
				+ tCities_Latitude + " INTEGER,"
				+ tCities_Longitude + " INTEGER"
		+ " );");
		db.execSQL("CREATE TABLE " + tLines + " ( "
				+ tLines_Id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ tLines_MapId + " INTEGER REFERENCES "
					+ tMaps + "(" + tMaps_Id + ") ON DELETE CASCADE,"
				+ tLines_Color + " INTEGER,"
				+ tLines_TrainDelay + " INTEGER,"
				+ tLines_Name + " TEXT NOT NULL"
		+ " );");
		db.execSQL("CREATE TABLE " + tStations + " ( "
				+ tStations_Id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ tStations_MapId + " INTEGER REFERENCES "
					+ tMaps + "(" + tMaps_Id + ") ON DELETE CASCADE,"
				+ tStations_Name + " TEXT NOT NULL,"
				+ tStations_Latitude + " INTEGER,"
				+ tStations_Longitude + " INTEGER,"
				+ tStations_Open + " INTEGER,"
				+ tStations_Close + " INTEGER,"
				+ tStations_PositionX + " INTEGER,"
				+ tStations_PositionY + " INTEGER,"
				+ tStations_LabelDx + " INTEGER,"
				+ tStations_LabelDy + " INTEGER,"
				+ tStations_LabelAngle + " INTEGER,"
				+ tStations_LabelVisible + " INTEGER"
		+ " );");
		db.execSQL("CREATE TABLE " + tTunnels + " ( "
				+ tTunnels_Id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ tTunnels_Time + " INTEGER,"
				+ tTunnels_FromStationId + " INTEGER REFERENCES "
					+ tStations + "(" + tStations_Id + ") ON DELETE CASCADE,"
				+ tTunnels_ToStationId + " INTEGER REFERENCES "
					+ tStations + "(" + tStations_Id + ") ON DELETE CASCADE,"
				+ tTunnels_LineId + " INTEGER REFERENCES "
					+ tLines + "(" + tLines_Id + ") ON DELETE CASCADE,"
				+ tTunnels_Smooth + " INTEGER"
		+ " );");
		db.execSQL("CREATE TABLE " + tTransfers + " ( "
				+ tTransfers_Id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ tTransfers_Time + " INTEGER,"
				+ tTransfers_FromStationId + " INTEGER REFERENCES "
					+ tStations + "(" + tStations_Id + ") ON DELETE CASCADE,"
				+ tTransfers_ToStationId + " INTEGER REFERENCES "
					+ tStations + "(" + tStations_Id + ") ON DELETE CASCADE"
		+ " );");
		db.execSQL("CREATE TABLE " + tCityAliases + " ( "
				+ tCityAliases_Id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ tCityAliases_CityId + " INTEGER REFERENCES "
					+ tCities + "(" + tCities_Id + ") ON DELETE CASCADE,"
				+ tCityAliases_Name + " TEXT NOT NULL"
		+ " );");
		db.execSQL("CREATE TABLE " + tStationAliases + " ( "
				+ tStationAliases_Id + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ tStationAliases_StationId + " INTEGER REFERENCES "
					+ tCities + "(" + tStations_Id + ") ON DELETE CASCADE, "
				+ tStationAliases_Name + " TEXT NOT NULL"
		+ " );");
		
		db.execSQL("CREATE INDEX " + iCities_Latitude + " ON " + tCities + " ( "
				+ tCities_Latitude
		+ " );");		
		db.execSQL("CREATE INDEX " + iCities_Longitude + " ON " + tCities + " ( "
				+ tCities_Longitude
		+ " );");
		db.execSQL("CREATE INDEX " + iCities_Name + " ON " + tCities + " ( "
				+ tCities_Name
		+ " );");
		db.execSQL("CREATE UNIQUE INDEX " + iCities_MapId + " ON " + tCities + " ( "
				+ tCities_MapId
		+ " );");

		db.execSQL("CREATE INDEX " + iLines_MapId + " ON " + tLines + " ( "
				+ tLines_MapId
		+ " );");		

		db.execSQL("CREATE INDEX " + iTransfers_FromStationId
				+ " ON " + tTransfers + " ( "
					+ tTransfers_FromStationId
		+ " );");		
		db.execSQL("CREATE INDEX " + iTunnels_FromStationId
				+ " ON " + tTunnels + " ( "
					+ tTunnels_FromStationId
		+ " );");
		
		db.execSQL("CREATE INDEX " + iStations_MapId + " ON " + tStations + " ( "
				+ tStations_MapId
		+ " );");		
		db.execSQL("CREATE INDEX " + iStations_Name + " ON " + tStations + " ( "
				+ tStations_Name
		+ " );");

		db.execSQL("CREATE INDEX " + iStationAliases_Name
				+ " ON " + tStationAliases + " ( "
					+ tStationAliases_Name
		+ " );");		
		db.execSQL("CREATE INDEX " + iStationAliases_StationId
				+ " ON " + tStationAliases + " ( "
					+ tStationAliases_StationId
		+ " );");

		db.execSQL("CREATE INDEX " + iCityAliases_Name
				+ " ON " + tCityAliases + " ( "
					+ tCityAliases_Name
		+ " );");		
		db.execSQL("CREATE INDEX " + iCityAliases_CityId
				+ " ON " + tCityAliases + " ( "
					+ tCityAliases_CityId
		+ " );");

		db.execSQL("CREATE TABLE " + TABLE_SETUP_INFO + " ( "
				+ "_ID INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ " setup_time DEFAULT CURRENT_TIME"
		+ " );");

	}
	
	/**
	 * @param db
	 */
	private static void dropTables(SQLiteDatabase db) {
		Logger.i("DbStorage: dropping tables");
		final String iCities_Name = indexName(CitiesTable.NAME,
				CitiesTable.Columns.NAME);
		final String iCities_Latitude = indexName(CitiesTable.NAME,
				CitiesTable.Columns.LATITUDE);
		final String iCities_Longitude = indexName(CitiesTable.NAME,
				CitiesTable.Columns.LONGITUDE);

		final String iLines_MapId = indexName(LinesTable.NAME,
				LinesTable.Columns.MAP_ID);

		final String iStations_Name = indexName(StationsTable.NAME,
				StationsTable.Columns.NAME);
		final String iStations_CityId = indexName(StationsTable.NAME,
				StationsTable.Columns.MAP_ID);

		final String iTunnels_FromStationId = indexName(TunnelsTable.NAME,
				TunnelsTable.Columns.FROM_STATION_ID);

		final String iTransfers_FromStationId = indexName(TransfersTable.NAME,
				TransfersTable.Columns.FROM_STATION_ID);

		final String iCityAliases_Name = indexName(CityAliasesTable.NAME,
				CityAliasesTable.Columns.NAME);
		final String iCityAliases_CityId = indexName(CityAliasesTable.NAME,
				CityAliasesTable.Columns.CITY_ID);

		final String iStationAliases_Name = indexName(
				StationAliasesTable.NAME, StationAliasesTable.Columns.NAME);
		final String iStationAliases_StationId = indexName(
				StationAliasesTable.NAME,
				StationAliasesTable.Columns.STATION_ID);

		db.execSQL("DROP INDEX IF EXISTS " + iCities_Latitude);
		db.execSQL("DROP INDEX IF EXISTS " + iCities_Longitude);
		db.execSQL("DROP INDEX IF EXISTS " + iCities_Name);
		db.execSQL("DROP INDEX IF EXISTS " + iLines_MapId);
		db.execSQL("DROP INDEX IF EXISTS " + iTransfers_FromStationId);
		db.execSQL("DROP INDEX IF EXISTS " + iTunnels_FromStationId);
		db.execSQL("DROP INDEX IF EXISTS " + iStations_CityId);
		db.execSQL("DROP INDEX IF EXISTS " + iStations_Name);
		db.execSQL("DROP INDEX IF EXISTS " + iStationAliases_Name);
		db.execSQL("DROP INDEX IF EXISTS " + iStationAliases_StationId);
		db.execSQL("DROP INDEX IF EXISTS " + iCityAliases_Name);
		db.execSQL("DROP INDEX IF EXISTS " + iCityAliases_CityId);

		db.execSQL("DROP TABLE IF EXISTS " + TABLE_SETUP_INFO);
		db.execSQL("DROP TABLE IF EXISTS " + StationAliasesTable.NAME);
		db.execSQL("DROP TABLE IF EXISTS " + CityAliasesTable.NAME);
		db.execSQL("DROP TABLE IF EXISTS " + StationsTable.NAME);
		db.execSQL("DROP TABLE IF EXISTS " + TransfersTable.NAME);
		db.execSQL("DROP TABLE IF EXISTS " + TunnelsTable.NAME);
		db.execSQL("DROP TABLE IF EXISTS " + LinesTable.NAME);
		db.execSQL("DROP TABLE IF EXISTS " + CitiesTable.NAME);
		db.execSQL("DROP TABLE IF EXISTS " + CountriesTable.NAME);
		db.execSQL("DROP TABLE IF EXISTS " + MapsTable.NAME);

	}

	static final String TABLE_SETUP_INFO = "setup_info";

	/**
	 * @author Eldar Abusalimov
	 */
	private static class DbHelper extends SQLiteOpenHelper {

		public static final String DB_NAME = "anderground_app.db";
		public static final int DB_VERSION = 6;

		/**
		 * @param context
		 */
		public DbHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			createTables(db);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			dropTables(db);
			createTables(db);
		}

	}

	private interface BaseColumns {

		/**
		 * The unique ID for a row.
		 * <P>
		 * Type: INTEGER (long)
		 * </P>
		 */
		public static final String _ID = "_id";

	}

	/**
	 * Maps table.
	 * 
	 * @author Eldar Abusalimov
	 */
	public static final class MapsTable {

		/**
		 * The name of the table
		 */
		public static final String NAME = "t_maps";
		
		/**
		 * Maps table columns.
		 * 
		 * @author Eldar Abusalimov
		 */
		public static final class Columns implements BaseColumns {
			
			/**
			 * The last modification date
			 * <P>
			 * Type: INTEGER (?)
			 * </P>
			 */
			public static final String LAST_MODIFIED = "last_modified";
			
			/**
			 * The private constructor.
			 */
			private Columns() {
			}
			
		}
		
		/**
		 * The private constructor.
		 */
		private MapsTable() {
		}
		
	}
	
	/**
	 * Cities table.
	 * 
	 * @author Eldar Abusalimov
	 */
	public static final class CitiesTable {

		/**
		 * The name of the table
		 */
		public static final String NAME = "t_cities";

		/**
		 * Cities table columns.
		 * 
		 * @author Eldar Abusalimov
		 */
		public static final class Columns implements BaseColumns {

			/**
			 * The id of the map
			 * <P>
			 * Type: INTEGER (foreign key to maps table on the _ID field, null
			 * on delete)
			 * </P>
			 */
			public static final String MAP_ID = "map_id";

			/**
			 * The id of the country
			 * <P>
			 * Type: INTEGER (foreign key to countries table on the _ID field)
			 * </P>
			 */
			public static final String COUNTRY_ID = "country_id";
			
			/**
			 * The city's name
			 * <P>
			 * Type: TEXT
			 * </P>
			 */
			public static final String NAME = "name";

			/**
			 * The city's url
			 * <P>
			 * Type: TEXT
			 * </P>
			 */
			public static final String URL = "url";
			
			/**
			 * The latitude
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String LATITUDE = "latitude";

			/**
			 * The longitude
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String LONGITUDE = "longitude";

			/**
			 * The private constructor.
			 */
			private Columns() {
			}

		}

		/**
		 * The private constructor.
		 */
		private CitiesTable() {
		}

	}

	/**
	 * Countries table.
	 * 
	 * @author Eldar Abusalimov
	 */
	public static final class CountriesTable {
		
		/**
		 * The name of the table
		 */
		public static final String NAME = "t_countries";
		
		/**
		 * Countries table columns.
		 * 
		 * @author Eldar Abusalimov
		 */
		public static final class Columns implements BaseColumns {
			
			/**
			 * The country code
			 * <P>
			 * Type: TEXT
			 * </P>
			 */
			public static final String CODE = "code";
			
			/**
			 * The country's name
			 * <P>
			 * Type: TEXT
			 * </P>
			 */
			public static final String NAME = "name";
			
			/**
			 * The private constructor.
			 */
			private Columns() {
			}
			
		}
		
		/**
		 * The private constructor.
		 */
		private CountriesTable() {
		}
		
	}
	
	/**
	 * Stations table.
	 * 
	 * @author Eldar Abusalimov
	 */
	public static final class StationsTable {

		/**
		 * The name of the table
		 */
		public static final String NAME = "t_stations";

		/**
		 * Stations table columns.
		 * 
		 * @author Eldar Abusalimov
		 */
		public static final class Columns implements BaseColumns {

			/**
			 * The id of the map this station belongs to
			 * <P>
			 * Type: INTEGER (foreign key to maps table on the _ID field)
			 * </P>
			 */
			public static final String MAP_ID = "map_id";

			/**
			 * The station's name
			 * <P>
			 * Type: TEXT
			 * </P>
			 */
			public static final String NAME = "name";

			/**
			 * The latitude
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String LATITUDE = "latitude";

			/**
			 * The longitude
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String LONGITUDE = "longitude";

			/**
			 * The opening time
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String OPEN = "open";
			
			/**
			 * The closing time
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String CLOSE = "close";
			
			/**
			 * The x position
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String POSITION_X = "pos_x";
			
			/**
			 * The y position
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String POSITION_Y = "pos_y";
			
			/**
			 * label dx
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String LABEL_DX = "label_dx";
			
			/**
			 * label dy
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String LABEL_DY = "label_dy";
			
			/**
			 * label visibility
			 * <P>
			 * Type: INTEGER (0 or 1)
			 * </P>
			 */
			public static final String LABEL_VISIBLE = "label_visible";
			
			/**
			 * label angle
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String LABEL_ANGLE = "label_angle";
			
			/**
			 * The private constructor.
			 */
			private Columns() {
			}

		}

		/**
		 * The private constructor.
		 */
		private StationsTable() {
		}

	}

	/**
	 * Lines table.
	 * 
	 * @author Eldar Abusalimov
	 */
	public static final class LinesTable {

		/**
		 * The name of the table
		 */
		public static final String NAME = "t_lines";

		/**
		 * Lines table columns.
		 * 
		 * @author Eldar Abusalimov
		 */
		public static final class Columns implements BaseColumns {

			/**
			 * The id of the map this line belongs to
			 * <P>
			 * Type: INTEGER (foreign key to maps table on the _ID field)
			 * </P>
			 */
			public static final String MAP_ID = "map_id";

			/**
			 * The name of the line
			 * <P>
			 * Type: TEXT
			 * </P>
			 */
			public static final String NAME = "name";

			/**
			 * The line's color
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String COLOR = "color";

			/**
			 * Trains' delay
			 * <P>
			 * Type: INTEGER
			 * </P>
			 */
			public static final String TRAIN_DELAY = "train_delay";
			
			/**
			 * The private constructor.
			 */
			private Columns() {
			}

		}

		/**
		 * The private constructor.
		 */
		private LinesTable() {
		}

	}

	/**
	 * Common columns for the {@link TransfersTable} and the
	 * {@link TunnelsTable}.
	 * 
	 * @author Eldar Abusalimov
	 */
	private interface TransitionColumns extends BaseColumns {

		/**
		 * The id of the "to" station
		 * <P>
		 * Type: INTEGER (foreign key to stations table on the _ID field)
		 * </P>
		 */
		public static final String TO_STATION_ID = "to_station_id";

		/**
		 * The id of the "from" station
		 * <P>
		 * Type: INTEGER (foreign key to stations table on the _ID field)
		 * </P>
		 */
		public static final String FROM_STATION_ID = "from_station_id";

		/**
		 * The time in seconds
		 * <P>
		 * Type: INTEGER
		 * </P>
		 */
		public static final String TIME = "time";

	}

	/**
	 * Transfers table.
	 * 
	 * @author Eldar Abusalimov
	 */
	public static final class TransfersTable {

		/**
		 * The name of the table
		 */
		public static final String NAME = "t_transfers";

		/**
		 * Transfers table columns.
		 * 
		 * @author Eldar Abusalimov
		 */
		public static final class Columns implements TransitionColumns {

			/**
			 * The private constructor.
			 */
			private Columns() {
			}

		}

		/**
		 * The private constructor.
		 */
		private TransfersTable() {
		}

	}

	/**
	 * Tunnels table.
	 * 
	 * @author Eldar Abusalimov
	 */
	public static final class TunnelsTable {

		/**
		 * The name of the table
		 */
		public static final String NAME = "t_tunnels";

		/**
		 * Tunnels table columns.
		 * 
		 * @author Eldar Abusalimov
		 */
		public static final class Columns implements TransitionColumns {

			/**
			 * The id of the line
			 * <P>
			 * Type: INTEGER (foreign key to lines table on the _ID field)
			 * </P>
			 */
			public static final String LINE_ID = "line_id";

			/**
			 * The smoothness
			 * <P>
			 * Type: INTEGER (0 or 1)
			 * </P>
			 */
			public static final String SMOOTH = "smooth";
			
			/**
			 * The private constructor.
			 */
			private Columns() {
			}

		}

		/**
		 * The private constructor.
		 */
		private TunnelsTable() {
		}

	}

	/**
	 * City names table.
	 * 
	 * @author Eldar Abusalimov
	 */
	public static final class CityAliasesTable {

		/**
		 * The name of the table
		 */
		public static final String NAME = "t_city_names";

		/**
		 * City names table columns.
		 * 
		 * @author Eldar Abusalimov
		 */
		public static final class Columns implements BaseColumns {

			/**
			 * The id of the city
			 * <P>
			 * Type: INTEGER (foreign key to cities table on the _ID field)
			 * </P>
			 */
			public static final String CITY_ID = "city_id";

			/**
			 * The name itself
			 * <P>
			 * Type: TEXT
			 * </P>
			 */
			public static final String NAME = "name";

			/**
			 * The default sort order for this table
			 */
			public static final String DEFAULT_SORT_ORDER = NAME + " ASC";

			/**
			 * The private constructor.
			 */
			private Columns() {
			}

		}

		/**
		 * The private constructor.
		 */
		private CityAliasesTable() {
		}

	}

	/**
	 * Station names table.
	 * 
	 * @author Eldar Abusalimov
	 */
	public static final class StationAliasesTable {

		/**
		 * The name of the table
		 */
		public static final String NAME = "t_station_names";

		/**
		 * Station names table columns.
		 * 
		 * @author Eldar Abusalimov
		 */
		public static final class Columns implements BaseColumns {

			/**
			 * The id of the station
			 * <P>
			 * Type: INTEGER (foreign key to stations table on the _ID field)
			 * </P>
			 */
			public static final String STATION_ID = "station_id";

			/**
			 * The name itself
			 * <P>
			 * Type: TEXT
			 * </P>
			 */
			public static final String NAME = "name";

			/**
			 * The default sort order for this table
			 */
			public static final String DEFAULT_SORT_ORDER = NAME + " ASC";

			/**
			 * The private constructor.
			 */
			private Columns() {
			}

		}

		/**
		 * The private constructor.
		 */
		private StationAliasesTable() {
		}

	}

}
